#!/env/python
import sqlite3

def parseJ1939ID(IDs,cursor):
    SA_Name={}
    SA_Note={}
    SPNs={}
    for IDText in IDs:
        PFText=IDText[-6:-4]
        PF=int(PFText,16)
        PSText=IDText[-4:-2]
        PS=int(PSText,16)
        SAText=IDText[-2:]
        SA=int(SAText,16)
        s='%i' %SA
        PriorityText=IDText[-8:-6]
        priority=int(PriorityText,16)>>2
        
        
        print('\nMessage ID: %s' %IDText)
        print('Priority (Hex): %s' %PriorityText)
        priority=int(PriorityText,16)>>2
        print('J1939 Priority: %i' %priority)
        print('PDU Format (PF) in hex: %s and in decimal: %i' %(PFText,PF))
        print('PDU Specific (PS) in hex: %s and in decimal: %i' %(PSText,PS))
        print('Source Address in hex: %s and in decimal: %i' %(SAText,SA)) 
        
        #Source Addresses
        if SA < 90 or SA > 247:
            sourceAddressData=cursor.execute('SELECT Name,Notes FROM SourceAddresses WHERE ID=?', [SA]).fetchone()
            SA_Name[SA]=sourceAddressData[0]
            SA_Note[SA]=sourceAddressData[1]
        elif SA > 159 and SA <248:
            sourceAddressData=cursor.execute('SELECT Name,Note FROM SourceAddressesOnHighWay WHERE SA=?', [s]).fetchone()
            SA_Name[SA]=sourceAddressData[0]
            SA_Note[SA]=sourceAddressData[1]
        else:
            SA_Name[SA]='SAE Future Use'
            SA_Note[SA]='Used SAE future Use or for dynamic address assignment'
        
        print('Source Controller: %s' %SA_Name[SA])
        
      
        PGNText=PFText+PSText
        PGN=int(PGNText,16)
        print('Parameter Group Number (PGN): %i' %PGN)
        try:    
            PGNData=cursor.execute('SELECT ParameterGroupLabel,TransmissionRate,Acronym FROM SPNandPGN WHERE PGN=?', [PGN]).fetchone()
            print('Parameter Group Label: %s' %PGNData[0])
        except TypeError:
             print('PGN from %s is not Defined in SAE J1939' %IDText)            
             continue
         
            
        print('Transmission Rate: %s' %PGNData[1]) 
        acronym=str(PGNData[2])
        print('Acronym: %s' %acronym)
        SPNs[acronym]=[]
        print('The Following SPNs are available in the message:')
        for SPNData in cursor.execute('SELECT SPN,Name,Units,Offset,Resolution FROM SPNandPGN WHERE PGN=?', [PGN]) :       
            try:
                SPN=int(SPNData[0])
            except ValueError:
                SPN=-1                
            
            Name=str(SPNData[1])
            Units=str(SPNData[2])
            
            try:
                Offset=float(SPNData[3])
            except ValueError:
                Offset=0
                
            res=SPNData[4].split(' ')
            res=res[0].split('/bit')
            res=res[0].split('/')
            try:

                if len(res)==2:
                    Resolution=float(res[0])/float(res[1])
                else:
                    Resolution=float(res[0])
            except ValueError:
                Resolution=0
            
            SPNs[acronym].append([SPN,Name,Units])
            #print(SPNData[4])
            print('SPN: %i, Name: %s, Unit: %s, Offset: %g, Resolution: %g' %(SPN,Name,Units,Offset,Resolution)) 
            
    return SA_Name,SA_Note,SPNs
    
def parseJ1939Message(IDText,payload,cursor):
    SPNs={}
    
    PFText=IDText[-6:-4]
    PF=int(PFText,16)
    PSText=IDText[-4:-2]
    PS=int(PSText,16)
    SAText=IDText[-2:]
    SA=int(SAText,16)
    s='%i' %SA
    PriorityText=IDText[-8:-6]
    priority=int(PriorityText,16)>>2
    
    
    print('\nMessage ID: %s' %IDText)
    print('Message data: %s' %payload)
    print('Priority (Hex): %s' %PriorityText)
    priority=int(PriorityText,16)>>2
    print('J1939 Priority: %i' %priority)
    print('PDU Format (PF) in hex: %s and in decimal: %i' %(PFText,PF))
    print('PDU Specific (PS) in hex: %s and in decimal: %i' %(PSText,PS))
    print('Source Address in hex: %s and in decimal: %i' %(SAText,SA)) 
        
    #Source Addresses
    if SA < 90 or SA > 247:
        sourceAddressData=cursor.execute('SELECT Name,Notes FROM SourceAddresses WHERE ID=?', [SA]).fetchone()
        SA_Name[SA]=sourceAddressData[0]
        SA_Note[SA]=sourceAddressData[1]
    elif SA > 159 and SA <248:
        sourceAddressData=cursor.execute('SELECT Name,Note FROM SourceAddressesOnHighWay WHERE SA=?', [s]).fetchone()
        SA_Name[SA]=sourceAddressData[0]
        SA_Note[SA]=sourceAddressData[1]
    else:
        SA_Name[SA]='SAE Future Use'
        SA_Note[SA]='Used SAE future Use or for dynamic address assignment'
    
    print('Source Controller: %s' %SA_Name[SA])
        
      
    PGNText=PFText+PSText
    PGN=int(PGNText,16)
    print('Parameter Group Number (PGN): %i' %PGN)
    try:    
        PGNData=cursor.execute('SELECT ParameterGroupLabel,TransmissionRate,Acronym FROM SPNandPGN WHERE PGN=?', [PGN]).fetchone()
        print('Parameter Group Label: %s' %PGNData[0])
           
         
         
            
        print('Transmission Rate: %s' %PGNData[1]) 
        acronym=str(PGNData[2])
        print('Acronym: %s' %acronym)
        SPNs[acronym]=[]
        print('The following SPN data are available in this message:')
        for SPNData in cursor.execute('SELECT SPN,Name,Units,Offset,Resolution,pos,SPNLength,Description FROM SPNandPGN WHERE PGN=?', [PGN]) :       
            try:
                SPN=int(SPNData[0])
                #print('SPN = %i' %SPN)
            except ValueError:
                SPN=-1                
            
            value='Unknown'
            Units='Unknown'
            meaning='Unknown'
            
            Name=str(SPNData[1])
            Units=str(SPNData[2])
            
            try:
                Offset=float(SPNData[3])
            except ValueError:
                Offset=0
                
            res=SPNData[4].split(' ')
            res=res[0].split('/bit')
            res=res[0].split('/')
            try:
    
                if len(res)==2:
                    Resolution=float(res[0])/float(res[1])
                else:
                    Resolution=float(res[0])
            except ValueError:
                Resolution=0
            
            SPNLength=int(SPNData[6])
            #print('SPNLength: %i' %SPNLength)
            if SPNLength<8:
                position=SPNData[5].split('.')
                #print(position)
                #print(int(position[0])-1)
                #print(payload)
                #print(payload[int(position[0])-1])
                binary=bin(int(payload[int(position[0])-1],16))
                binText=binary[2:].zfill(8)
                #print(binText)
                value=binText[int(position[1][0])-1:int(position[1][0])-1+SPNLength]
                #print(value)
                for descText in SPNData[7].split('\n'):
                    #print(descText[:SPNLength])
                    if value==descText[:SPNLength]:
                        meaning=descText[SPNLength:].split('-')[-1].strip()
                        break    
                        
                    
            elif SPNLength==8:
                position=int(SPNData[5])
    
                binary=int(payload[position-1],16)
                if binary==255:
                    value='Out of Range (0xFF)'
                else:
                    value=Resolution*binary+Offset
                #print(value)
                meaning=SPNData[7][0:35]
            elif SPNLength==16:
                position=int(SPNData[5][0])
    
                binary=int(payload[position]+payload[position-1],16) #reverse byte order
                if binary==2**16:
                    value='Out of Range (0xFF)'
                else:
                    value=Resolution*binary+Offset
                #print(value)
                meaning=SPNData[7][0:35]
            else:
                meaning=SPNData[7][0:35]
                
            SPNs[acronym].append([SPN,Name,value,Units,meaning])
            
            print('SPN: %i, Name: %s, Value: %s, Unit: %s, Meaning: %s' %(SPN,Name,str(value),Units,meaning) )
        
    except TypeError:
            print('PGN from %s is not Defined in SAE J1939' %IDText)     
    
    return SPNs
    
    #result=cursor.execute('SELECT Name,Notes FROM SourceAddresses WHERE ID=0')
    #    print(result.fetchone())
    
    
filename='TIB30317000.xls'
fileHandle=open(filename,'r')
datalines=fileHandle.readlines()
fileHandle.close()
print('Decode of File %s' %filename)
#print(datalines[0:100])

DLCs={}
timeList=[]
IDList=[]
rawData=[]

for line in datalines[1:]:
    try:
        #print(line)
        elements=line.split(',')
        #print(elements)
        ID=elements[7]
        IDList.append(ID)        
        #print(ID)
        timeList.append(float(elements[0]))        
        
        DLCText=elements[5].split(':')
        #print(DLCText)
   
        DLC=DLCText[1]
        #print(DLC)
        DLCs[ID]=int(DLC)
        
        rawData.append(elements[9:-1])        
       # DLC1[ID]=len(rawData[-1])
        
    except IndexError:
        pass


IDs=DLCs.keys()


db=sqlite3.connect('J1939.db')
db.text_factory = str # This command enables the strings to be decoded by the sqlite commands
cur = db.cursor()


SA_Name,SA_Note,SPNs=parseJ1939ID(IDs,cur)

print ('\nSource Address,Name')    
for SA in sorted(SA_Name.keys()):
    print('%i,%s' %(SA,SA_Name[SA])) 

print('The following information is broadcast:')    
for PGN in SPNs.keys():
    print('Parameter Group: %s' %PGN)
    for SPN in SPNs[PGN]:
        print(SPN)

#for ID,raw in zip(IDList,rawData):
#    #if ID[-6:-2]=='f003':    
#        SPNs=parseJ1939Message(ID,raw,cur)
#                
    
db.close()